package com.amumu.netctoss.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Repository;

import com.amumu.netctoss.dao.ReportDao;
import com.amumu.netctoss.entity.Host;
import com.amumu.netctoss.vo.ClientUsingHost;
import com.amumu.netctoss.vo.DurationClientUsed;

@Repository
@Scope("prototype")
public class HibernateReportDao extends HibernateBaseDao implements ReportDao {

	public List<DurationClientUsed> findClient(int page, int size)
			throws Exception {
		final String sql = "select a.id,a.login_name,a.real_name,to_number(a.idcard_no),a.telephone,to_char(sd.logout_time,'yyyymm'),sum(sd.duration) from account a left outer join ( service s  join service_detail sd on s.id=sd.service_id ) on a.id=s.account_id group by a.id,a.login_name,a.real_name,a.idcard_no,a.telephone,to_char(sd.logout_time,'yyyymm') order by a.id,to_char(sd.logout_time,'yyyymm')";
		List<Object[]> list = findByPageSQL(page, size, sql, null);
		List<DurationClientUsed> clients = new ArrayList<DurationClientUsed>();
		for(Object[] o:list){
			DurationClientUsed c = new DurationClientUsed();
			c.setAccountId(Integer.valueOf(o[0].toString()));
			c.setLoginName(o[1].toString());
			c.setRealName(o[2].toString());
			c.setIdcardNo(o[3].toString());
			c.setTelephone(o[4].toString());
			c.setMonth((String)o[5]);
			if(o[6]!=null)	c.setDuration(Long.valueOf(o[6].toString()));
			
			clients.add(c);
			
		}
		return clients;
	}

	public List<Host> findHost(int page, int size) throws Exception {
		String hql = "from Host";
		return findByPage(page, size, hql, null);
	}

	public List<ClientUsingHost> findClientHost(int page, int size)
			throws Exception {
		final String sql = "select * from (select s.unix_host,a.login_name,a.real_name,to_number(a.idcard_no),sum(sd.duration) sumd,row_number() over (partition by s.unix_host order by sum(sd.duration) desc) rn from account a,service s,service_detail sd where a.id=s.account_id and sd.service_id=s.id group by s.unix_host,a.login_name,a.real_name,a.idcard_no) where rn<=3";
		List<Object[]> list = findByPageSQL(page, size, sql, null);
		List<ClientUsingHost> cuhs = new ArrayList<ClientUsingHost>();
		for(Object[] o:list){
			ClientUsingHost c = new ClientUsingHost();
			System.out.println("length:"+o.length);
			//System.out.println("0:"+o[0]);
			//System.out.println("2:"+o[2]);
			//System.out.println("3:"+o[3]);
			c.setUnixHost((String) o[0]);
			c.setLoginName((String) o[1]);
			c.setRealName((String) o[2]);
			//System.out.println("4:"+o[4]);
			//System.out.println("5:"+o[5]);
			c.setIdcardNo(o[3].toString());
			c.setDuration(Long.valueOf(o[4].toString()));
			
			cuhs.add(c);
		}
		return cuhs;
	}

	public int getPageCount(int size) {
		String sql = "select count(distinct a.id) from account a left outer join ( service s join service_detail sd on s.id=sd.service_id ) on a.id=s.account_id";
		return getTotalPageSQL(size, sql, null);
	}

	public int getPageCount1(int size) {
		String hql = "select count(id) from Host";
		return getTotalPage(size, hql, null);
	}

	public int getPageCount2(int size) {
		String sql = "select count(*) from (select s.unix_host,a.login_name,a.real_name,to_number(a.idcard_no),sum(sd.duration) sumd,row_number() over (partition by s.unix_host order by sum(sd.duration) desc) rn from account a,service s,service_detail sd where a.id=s.account_id and sd.service_id=s.id group by s.unix_host,a.login_name,a.real_name,a.idcard_no) where rn<=3";
		return getTotalPageSQL(size, sql, null);
	}
	
	

}
